package cvs;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class GSBNandPOSSync {

	public static void main(String[] args) {
		GSBNandPOSSync sync = new GSBNandPOSSync();

		System.out.println("Start sync");

		sync.doit();

		System.out.println("end sync");
	}

	public void doit() {
		ArrayList<HashMap<String, String>> list = buildUnmatched();

		DBConnectorPOS posconnection = new DBConnectorPOS();
		DBConnectorGSBN gsbnconnection = new DBConnectorGSBN();

		try {
			posconnection.start();
			gsbnconnection.start();

			Connection gsbn_connection = gsbnconnection.getConnection();
			Connection pos_connection = posconnection.getConnection();

			for (HashMap<String, String> row : list) {
				int posID = findPOS(row, pos_connection);

				if (posID == 0) {
					posID = insertPOS(row, pos_connection);
					updateGSBN(posID, row.get("id"), gsbn_connection);
					insertGSBN(row, gsbn_connection, posID);
				} else {
					updateGSBN(posID, row.get("id"), gsbn_connection);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			posconnection.close();
			gsbnconnection.close();
		}
	}

	public void updateGSBN(int id, String gsbnID, Connection connection)
			throws SQLException {
		String sql = "update tb_CustomerOrg set temp= pos_id, pos_id=? where OrgID=? ";

		PreparedStatement ps;

		ps = connection.prepareStatement(sql);

		int i = 0;
		ps.setInt(++i, id);
		ps.setString(++i, gsbnID);

		ps.execute();
	}

	public int findPOS(HashMap<String, String> row, Connection connection)
			throws Exception {
		int posID = 0;

		String sql = "select master_id from customer "
				+ "where customer_name=? and street1=? and city=? and state=? and zipcode=? and "
				+ "internal_name=? and internal_address=? and internal_zip=?  ";

		PreparedStatement ps;
		ResultSet rs;

		ps = connection.prepareStatement(sql);

		int i = 0;

		ps.setString(++i, row.get("name"));
		ps.setString(++i, row.get("address"));
		ps.setString(++i, row.get("city"));
		ps.setString(++i, row.get("state"));
		ps.setString(++i, row.get("zip"));

		ps.setString(++i, row.get("internal_name"));
		ps.setString(++i, row.get("internal_address"));
		ps.setString(++i, row.get("internal_zip"));

		rs = ps.executeQuery();
		if (rs.next())
			posID = rs.getInt(1);

		return posID;
	}

	public int insertPOS(HashMap<String, String> row, Connection connection)
			throws Exception {
		String sql = "insert into customer " + "(master_id, notes,  "
				+ "customer_name, street1, city, state, zipcode, "
				+ "internal_name, internal_address, internal_zip  ) "
				+ "values(-1, 'gsbn', ?,?,?,?,? ,?,?,? );";

		String sqlID = "select max(id) id from CUSTOMER where notes='gsbn' ";

		PreparedStatement ps;
		ResultSet rs;

		int posID = 0;

		ps = connection.prepareStatement(sql);

		int i = 0;

		ps.setString(++i, row.get("name"));
		ps.setString(++i, row.get("address"));
		ps.setString(++i, row.get("city"));
		ps.setString(++i, row.get("state"));
		ps.setString(++i, row.get("zip"));

		ps.setString(++i, row.get("internal_name"));
		ps.setString(++i, row.get("internal_address"));
		ps.setString(++i, row.get("internal_zip"));

		ps.execute();

		ps = connection.prepareStatement(sqlID);

		rs = ps.executeQuery();
		if (rs.next())
			posID = rs.getInt("id");

		return posID;
	}

	public void insertGSBN(HashMap<String, String> row, Connection connection,
			int posID) throws Exception {
		String sql = "INSERT INTO customer_master (master_id,customer_name,street1,city,state,  zipcode,internal_name,internal_address,internal_zip,id) "
				+ "VALUES(?,?,?,?,? ,?,?,?,?,?); ";

		PreparedStatement ps;

		ps = connection.prepareStatement(sql);

		int i = 0;

		ps.setInt(++i, posID);
		ps.setString(++i, row.get("name"));
		ps.setString(++i, row.get("address"));
		ps.setString(++i, row.get("city"));
		ps.setString(++i, row.get("state"));

		ps.setString(++i, row.get("zip"));
		ps.setString(++i, row.get("internal_name"));
		ps.setString(++i, row.get("internal_address"));
		ps.setString(++i, row.get("internal_zip"));
		ps.setInt(++i, posID);

		ps.execute();
	}

	public ArrayList<HashMap<String, String>> buildUnmatched() {
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		String sql = "select o.orgid id, o.OrgName name, o.address, o.city, o.StateCode state, o.ZipCode zip, "
				+ " o.internal_name, o.internal_address, o.internal_zip "
				+ "from tb_CustomerOrg o where o.pos_id <= 0";

		PreparedStatement ps;
		ResultSet rs;

		DBConnectorGSBN connector = new DBConnectorGSBN();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				HashMap<String, String> row = new HashMap<String, String>();

				row.put("id", rs.getString("id"));
				row.put("name", rs.getString("name"));
				row.put("address", rs.getString("address"));
				row.put("city", rs.getString("city"));
				row.put("state", rs.getString("state"));
				row.put("zip", rs.getString("zip"));
				row.put("internal_name", rs.getString("internal_name"));
				row.put("internal_address", rs.getString("internal_address"));
				row.put("internal_zip", rs.getString("internal_zip"));

				list.add(row);

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}

		return list;
	}

}
